import pymysql.cursors
import numpy as np 
import pymysql
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class  Mysqlutils(object):
    def __init__(self):
        self.conn = pymysql.connect(

            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8'        ) 
    
    def is_holiday(self,data):
        """是否节假日判断"
        """
        if data in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04','2024-10-05','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03']:
            return 1
        return 0 
    def get_scenic_data(self):
        cursor = self.com.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
       SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count
        FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date,hour
        """

        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # print(df)
        #格式转换
        date_range = pd.date_range(start= '2024-07-01',end = '2025-03-01',freq='D')
        hours = range(6,24)
        full_index = pd.MultiIndex.from_product([date_range,hours],names=['date','hour'])
        df_full = df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        #按天组织数据，每行包含18个小时段的检票次数
        df_pivot = df_full.pivot(index='date',colums='hour',values='count')
        df_pivot['dow'] = df_pivot.index.dayofweek
        df_pivot['month'] = df_pivot.index.month
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        df_pivot = pd.get_dummies(df_pivot,columns=['dow','month'],dtype=int)
        print(df_pivot.head)


if __name__ == '__main__':
    mu = Mysqlutils()
    mu.get_scenic_data()

